﻿#include "stdafx.h"
#include "TakesDB.h"
#include <string>

TakesDB::TakesDB()
{
}

Takes TakesDB::selectByStudentIdAndCourseId(const std::string &si, const std::string &ci)
{
	Takes takes;
	try {
		Connection *con;
		Statement *stmt;
		ResultSet *res;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		//cout << "数据库连接成功！" << endl;
		stmt = con->createStatement();
		stmt->execute("set names gbk");
		string s = "SELECT * from takes where stu_id='" + si + "' and course_id='" + ci + "'";
		cout << s <<endl;
		res = stmt->executeQuery(s);

		while (res->next()) {
			string gradeId = res->getString(3);
			takes.setStuId(si);
			takes.setGradeId(gradeId);
			takes.setCourseId(ci);
		}
		delete res;
		delete stmt;
		delete con;
		return takes;
	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}
}

list<Takes> TakesDB::selectAllByStudentId(const std::string &si)
{
	list<Takes> takes;
	try {
		Connection *con;
		Statement *stmt;
		ResultSet *res;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		//cout << "数据库连接成功！" << endl;
		stmt = con->createStatement();
		stmt->execute("set names gbk");
		res = stmt->executeQuery("SELECT * from takes  where stu_id='" + si + "'");

		while (res->next()) {
			//cout << "\t... MySQL replies: ";
			/* 获取某列属性值通过列名 */
			//cout << res->getString("_message") << endl;
			//cout << "\t... MySQL says it again: ";
			/* 通过数字偏移量, 1 代表第一列 */
			string sid = res->getString(1);
			string cid = res->getString(2);
			string gid = res->getString(3);

			Takes take;
			take.setStuId(sid);
			take.setCourseId(cid);
			take.setGradeId(gid);

			takes.push_back(take);
		}
		delete res;
		delete stmt;
		delete con;
		return takes;
	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}
}

list<Takes> TakesDB::selectAllByCourseId(const std::string &ci)
{
	list<Takes> takes;
	try {
		Connection *con;
		Statement *stmt;
		ResultSet *res;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		//cout << "数据库连接成功！" << endl;
		stmt = con->createStatement();
		stmt->execute("set names gbk");
		res = stmt->executeQuery("SELECT * from takes  where course_id='" + ci + "'");

		while (res->next()) {
			//cout << "\t... MySQL replies: ";
			/* 获取某列属性值通过列名 */
			//cout << res->getString("_message") << endl;
			//cout << "\t... MySQL says it again: ";
			/* 通过数字偏移量, 1 代表第一列 */
			string sid = res->getString(1);
			string cid = res->getString(2);
			string gid = res->getString(3);

			Takes take;
			take.setStuId(sid);
			take.setCourseId(cid);
			take.setGradeId(gid);

			takes.push_back(take);
		}
		delete res;
		delete stmt;
		delete con;
		return takes;
	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}
}

list<Takes> TakesDB::selectAll()
{
	list<Takes> takes;
	try {
		Connection *con;
		Statement *stmt;
		ResultSet *res;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		//cout << "数据库连接成功！" << endl;
		stmt = con->createStatement();
		stmt->execute("set names gbk");
		res = stmt->executeQuery("SELECT * from takes");

		while (res->next()) {
			//cout << "\t... MySQL replies: ";
			/* 获取某列属性值通过列名 */
			//cout << res->getString("_message") << endl;
			//cout << "\t... MySQL says it again: ";
			/* 通过数字偏移量, 1 代表第一列 */
			string sid = res->getString(1);
			string cid = res->getString(2);
			string gid = res->getString(3);

			Takes take;
			take.setStuId(sid);
			take.setCourseId(cid);
			take.setGradeId(gid);

			takes.push_back(take);
		}
		delete res;
		delete stmt;
		delete con;
		return takes;
	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}
}
void TakesDB::deleteByStudentIdAndCourseId(const std::string &si, const std::string &ci)
{

	try {
		Connection *con;
		Statement *stmt;
		ResultSet *res;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		//cout << "数据库连接成功！" << endl;
		stmt = con->createStatement();
		stmt->execute("set names gbk");
		stmt->executeUpdate("DELETE from takes where stu_id='" + si + "' and course_id='" + ci + "'");

		delete stmt;
		delete con;
	}
		catch (sql::SQLException &e) {
			cout << "# ERR: SQLException in " << __FILE__;
			cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
			cout << "# ERR: " << e.what();
			cout << " (MySQL error code: " << e.getErrorCode();
			cout << ", SQLState: " << e.getSQLState() << " )" << endl;
		}
}
void TakesDB::update(Takes &t)//删除成绩
{
	try {
		Connection *con;
		Statement *stmt;
		ResultSet * res;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		//cout << "数据库连接成功！" << endl;
		stmt = con->createStatement();
		stmt->execute("set names gbk");
		stmt->executeUpdate("update takes set grade_id='" + t.getGradeId() +  "' where stu_id='" + t.getStuId() + "' and course_id='" + t.getCourseId()+"'");
		delete stmt;
		delete con;
	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}
}
void TakesDB::insert(Takes &t)
{

	try {
		Connection *con;
		Statement *stmt;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		stmt = con->createStatement();
		stmt->execute("set names gbk");
		stmt->executeUpdate("insert into takes values('" + t.getStuId() + "','" +t.getCourseId() + "','" + t.getGradeId() + "')");
		delete stmt;
		delete con;
	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}
}

TakesDB::~TakesDB()
{
}
